# coding:utf-8
import datetime

import pymysql
from flask import flash, render_template, redirect, url_for, request, make_response
from flask import jsonify
from flask_login import current_user

import config
from app.main import main
from app.main.forms import ComplaintForm
from app.task import async_send_email


# home_page
@main.route('/', methods=["POST", "GET"])
def home():
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
	password='123456',
        db='mytest',
        charset='utf8')
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
    form = ComplaintForm()
    if request.method == "POST":
        if form.validate_on_submit():
            now = datetime.datetime.now()
            complained_id = int(form.student.data)
            try:
                cur.execute(
                    "INSERT INTO complain(student_id,complaint_date,content,complained_id) VALUES('%d','%s','%s','%d')" % (
                    int(current_user.id), now.strftime('%Y-%m-%d'), form.body.data, complained_id))
                conn.commit()
                if complained_id == 0:
                    sendMail('New unknown complain', config.ADMIN_EMAIL,
                             "<p>New unknown complain.Click<a href='{{url_for('main.home',token=token,_external=True)}}'> here</a> to check...</p>")
                else:
                    cur.execute('select email,name from student where id="%d"' % complained_id)
                    item = cur.fetchone()
                    email = item['email']
                    sendMail('Receive complaints', email, render_template('mail/confirm-mail.html', user=item['name']))
                    sendMail('Calling for Feedback', current_user.email,
                             render_template('mail/revisit-mail.html', user=current_user), 14)
            except Exception:
                pass
            conn.close()
            return redirect(url_for('main.home'))

    show_mine = bool(request.cookies.get('show_mine', ''))

    complain_list = []
    complained_list = []
    revisit_list = []
    if current_user.is_authenticated:
        if current_user.name == 'admin':
            return redirect(url_for('main.home2'))
        cur.execute('select * from complain where student_id=%d order by complaint_date' % current_user.id)
        complain_list = cur.fetchall()
        for i in range(len(complain_list)):
            complain = complain_list[i]
            list2 = [complain.get('content'), complain.get('state'), complain.get('complaint_date')]
            complain_list[i] = list2
        cur.execute('select * from complain where complained_id=%d order by complaint_date' % current_user.id)
        complained_list = cur.fetchall()
        for i in range(len(complained_list)):
            complained = complained_list[i]
            list3 = [complained.get('content'), complained.get('state'), complained.get('complaint_date')]
            complained_list[i] = list3
        cur.execute(
            "select * from complain where student_id=%d and state='to feedback' and complaint_date<'%s' order by complaint_date" % (
            current_user.id, (datetime.datetime.now() - datetime.timedelta(weeks=1)).strftime('%Y-%m-%d')))
        revisit_list = cur.fetchall()
        for i in range(len(revisit_list)):
            rev = revisit_list[i]
            list3 = [rev.get('content'), rev.get('state'), rev.get('complaint_date'), rev.get('id')]
            revisit_list[i] = list3
    else:
        return redirect(url_for('auth.login'))
    cur.close()
    conn.commit()
    conn.close()
    return render_template('home_page.html', form=form, show_mine=show_mine, complain_list=complain_list,
                           complained_list=complained_list, revisit_list=revisit_list)


@main.route('/admin', methods=["POST", "GET"])
def home2():
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123456',
	db='mytest',
        charset='utf8')
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
    show_mine = bool(request.cookies.get('show_mine', ''))
    complain_list = []
    handled_list = []
    noimprovved_list = []
    if current_user.is_authenticated:
        cur.execute(
            'SELECT complained_id,name,COUNT(complained_id) num FROM student,complain WHERE student.id=complain.complained_id GROUP BY complained_id ORDER BY num DESC')
        complain_list = cur.fetchall()
        for i in range(len(complain_list)):
            complain = complain_list[i]
            list2 = [complain.get('complained_id'), complain.get('name'), complain.get('num')]
            complain_list[i] = list2

        cur.execute("select * from complain where handle_date != 'NULL' order by handle_date")
        handled_list = cur.fetchall()
        for i in range(len(handled_list)):
            rev = handled_list[i]
            list3 = [rev.get('content'), rev.get('state'), rev.get('handle_date')]
            handled_list[i] = list3
        cur.execute("select * from complain where state='no-improve' order by complaint_date")
        noimprovved_list = cur.fetchall()
        for i in range(len(noimprovved_list)):
            rev = noimprovved_list[i]
            list3 = [rev.get('student_id'), rev.get('content'), rev.get('complaint_date')]
            noimprovved_list[i] = list3
    else:
        return redirect(url_for('auth.login'))
    cur.close()
    conn.commit()
    conn.close()
    return render_template('home_page_admin.html', username=config.ADMIN_USER, show_mine=show_mine,
                           complain_list=complain_list, handled_list=handled_list, noimprovved_list=noimprovved_list)


@main.route('/show_mine')
def show_mine():
    resp = make_response(redirect(url_for('main.home')))
    resp.set_cookie('show_mine', '1', max_age=24 * 60 * 60)
    return resp


# delay ：day
def sendMail(title, to, body, delay=0):
    try:
        msg = dict()
        msg['subject'] = title
        msg['sender'] = config.MAIL_USERNAME
        msg['recipients'] = [to]
        msg['attach'] = body
        async_send_email.apply_async(args=[msg], countdown=delay * 24 * 60)
        if delay == 0:
            flash('Sending email to {0}'.format(to))
        else:
            flash('Sending email to {0} in {1} days'.format(to, delay))
    except Exception as e:
        print(e)


# be complained
@main.route('/getComplained', methods=["POST", "GET"])
def getComplained():
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123456',
	db='mytest',
        charset='utf8')
    student_id = request.form.get('student_id')

    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cur.execute(
        'select name,content,state,complaint_date,complain.id id from student,complain where complained_id=%d and student.id =complain.complained_id order by complaint_date' % int(
            student_id))
    complained_list = cur.fetchall()

    for i in range(len(complained_list)):
        complained = complained_list[i]
        # 计算相差天数
        delta = (datetime.datetime.now().date() - complained.get('complaint_date')).days
        list3 = [complained.get('name'), complained.get('content'), complained.get('state'),
                 str(complained.get('complaint_date')),complained.get('id'),delta]
        complained_list[i] = list3
    return jsonify(complained_list)


# handle send mail
@main.route('/handle', methods=["POST", "GET"])
def handle():
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123456',
	db='mytest',
        charset='utf8')
    complaint_id = request.form.get('complaint_id')
    words = request.form.get('words')
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cur.execute(
        'select email,name from student,complain where complain.id=%d and student.id =complain.complained_id' % int(
            complaint_id))
    stu = cur.fetchone()
    sendMail("Teacher's message", stu['email'], words)
    cur.execute("update complain set handle_date='%s' where id = %d" % (
    datetime.datetime.now().strftime('%Y-%m-%d'), int(complaint_id)))
    flash('Handle Done!!!')
    cur.close()
    conn.commit()
    conn.close()
    return redirect(url_for('main.home2'))


@main.route('/updateState', methods=["POST", "GET"])
def updateState():
    complaint_id = request.form.get('complaint_id')
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123456',
	db='mytest',
        charset='utf8')
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
    flag = request.form.get('improved')
    print(flag, complaint_id)
    if int(flag) == 1:
        cur.execute("update complain set state='%s' where id = %d" % (
            'improve', int(complaint_id)))
    elif int(flag) == 0:
        cur.execute("update complain set state='%s' where id = %d" % (
            'no-improve', int(complaint_id)))
    cur.close()
    conn.commit()
    conn.close()
    flash('Review Done!!!')
    return redirect(url_for('main.home'))
